<?php

namespace App\Http\Controllers\Admin\ExportOrigin;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use Illuminate\Support\Facades\Storage;
use App\Http\Controllers\Admin\ItemDataController;
use App\Admin\SampleInfo;

class PublicController extends Controller
{
    /* 导出Excel 检测报告*/
    public function export(Request $request,$storeFile=''){
        // if (!isAdmin()) {
        //     return '升级中。。。。';
        // }
        $sampleId = $request->id;
        if($sampleId){ //如果是单样品原始记录直接导出
            return $this->exportRes();
        }
        set_time_limit(0);
        ini_set('max_execution_time', 0);
        ini_set('memory_limit', '-1');
        $temp = './storage/ExcelTemplate/原始记录.xls'; //考虑到兼容Linux，斜杠用“/”
        if (!file_exists($temp)) {
            return '原始记录模板文件不存在';
        }
        $contract_id = $request->contract_id;
        $batchNum = DB::table('a_contract')->where('id',$contract_id)->value('batchNum');
        $sampleIds = DB::table('a_sample_info as a')
            ->leftJoin('a_contract_gaiyao as b','a.contract_gaiyao_id','=','b.id')
            ->where('b.contract_id',$contract_id)
            ->orderBy('a.id','desc')
            ->pluck('a.bar_code','a.id')->toArray();
        $dir = "sampleOrigin/{$batchNum}";
        // dd($dir);
        foreach ($sampleIds as $sampleId => $barCode) {
            $request->offsetSet('id',$sampleId);
            $this->exportRes("{$dir}/{$barCode}.xls");
        }

        $disk = Storage::disk('public');
        $spreadsheet = $spreadsheet2 = $worksheet =null;
        foreach ($sampleIds as $sampleId => $barCode) {
            $temp = $disk->path("{$dir}/{$barCode}.xls");
            if (!file_exists($temp)) {
                continue;
            }
            if (!$spreadsheet) {
                $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($temp); //读取模板文件
            }else{
                $spreadsheet2 = \PhpOffice\PhpSpreadsheet\IOFactory::load($temp); //读取模板文件
                $worksheet2 = $spreadsheet2->getSheet(0);
                $spreadsheet->addExternalSheet($worksheet2,0); //添加外部工作表
                $spreadsheet2->disconnectWorksheets();
                unset($spreadsheet2);
            }
        }
        //----------直接在浏览器下载
        $file_name = "原始记录-批次{$batchNum}.xls"; //输出的文件名称
        $disk->deleteDirectory($dir);
        return self::saveDown($spreadsheet,$file_name,$storeFile);
    }

    //保存并下载
    public static function saveDown($spreadsheet,$file_name,$storeFile=''){
        //----------直接在浏览器下载----start
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
        if ($storeFile) {
            $disk = Storage::disk('public');
            $disk->put($storeFile,''); //创建路径
            $writer->save($disk->path($storeFile));
            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet);
            return $disk->path($storeFile);
        }

        // header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出Excel 07文件
        header('Content-Type: application/vnd.ms-excel'); //告诉浏览器输出Excel 03版本文件
        header('Content-Disposition: attachment;filename=' . urlencode($file_name)); //告诉浏览器输出的文件名称
        header('Cache-Control: max-age=0'); //禁止缓存
        //----------直接在浏览器下载----end

        // $writer->save('write.xls');//将Excel文件保存在服务器的磁盘上
        $writer->setPreCalculateFormulas(false); //禁用公式预先计算
        $writer->save('php://output'); //直接在浏览器下载

        //为了防止内存泄露，建议用完手动清理
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }

    /*导出结果（原始记录）*/
    public function exportRes($storeFile=''){
        $template_file = './storage/ExcelTemplate/原始记录.xls'; //考虑到兼容Linux，斜杠用“/”
        if (!file_exists($template_file)) {
            return '原始记录模板文件不存在';
        }
        $request = request();
        $id = $request->id;
        $info = DB::table('a_sample_info as a')
            ->leftJoin('a_contract_gaiyao as b','a.contract_gaiyao_id','=','b.id')
            ->leftJoin('a_contract as c','a.contract_id','=','c.id')
            ->leftJoin('a_status_time as d','a.contract_id','=','d.contract_id')
            ->where('a.id',$id)
            ->select('b.tool_name','a.bar_code','a.self_num','a.gqj_ID','b.cycle','b.std_num','b.voltage','b.class','a.sample_status','a.manufacturer','a.production_date','a.guige_xinghao','a.brand','a.remark','a.status0','a.audit_man','c.audit_man as pi_man','d.check_end','d.approve_end','b.class_id','b.vol_id','b.std_id','b.tool_id')
            ->first();
        if(!$info){
            // dd(request()->all());
            return '样品不存在！';
        }
        $itemDataCon = new ItemDataController();
        $info->cycle = $itemDataCon->get_cycle($info).'年';
        $baseInfo = [
            'C2' => $info->bar_code,
            'C3' => $info->gqj_ID,
            'C4' => $info->tool_name,
            'C5' => $info->std_num,
            'C6' => $info->voltage,
            'C7' => $info->class,
            'C8' => $info->sample_status,
            'H2' => $info->self_num,
            'H3' => $info->manufacturer,
            'H4' => $info->production_date,
            'H5' => $info->guige_xinghao,
            'H6' => $info->brand,
            'H7' => $info->cycle,
            'H8' => $info->remark,
            'M2' => SampleInfo::dbStat('status0')[$info->status0],
            'M3' => $info->audit_man,
            'M4' => $info->check_end,
            'M5' => $info->pi_man,
            'M6' => $info->approve_end
        ];
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($template_file); //读取模板文件
        $worksheet = $spreadsheet->getSheet(0); //按索引获取表单，从 0 开始
        foreach ($baseInfo as $cell => $value) {
            $worksheet->setCellValue($cell,$value);
        }
        $itemData = DB::table('a_item_data as a')
            ->leftJoin('a_testitem as b','a.item_id','=','b.id')
            ->leftJoin('manager as c','a.manager_id','=','c.id')
            ->where('a.sample_info_id',$id)
            ->select('b.name as item_name','c.realname as realname','a.utime','a.remark','a.item_id','a.res')
            ->get();
        $rowIndex = $initRowIndex = 11;
        $initHeight = $worksheet->getRowDimension($rowIndex)->getRowHeight();
        $mergecells = [];
        $allMerge = $worksheet->getMergeCells();
        foreach ($allMerge as $mergecell) {
            if(strstr($mergecell, $rowIndex.':')){
                $mergecells[] = $mergecell;
            }
        }
        // dd($allMerge,$mergecells);
        foreach ($itemData as $k=>$item) {
            if ($k > 0) {
                $worksheet->insertNewRowBefore($rowIndex,1);
                foreach ($mergecells as $mergecell) {
                    $worksheet->mergeCells(str_replace($initRowIndex,$rowIndex,$mergecell));
                }
            }
            $worksheet->setCellValue('A'.$rowIndex,$item->item_name);
            $worksheet->setCellValue('C'.$rowIndex,$item->realname);
            $utime = $item->utime ? date('Y年m月d日',strtotime($item->utime)) : '';
            $worksheet->setCellValue('E'.$rowIndex,$utime);
            /***************************检验标准  start******************************/
            $yaoqiu = DB::table('a_std_tool_item_rel')->where([
                ['std_id','=',$info->std_id],
                ['tool_id','=',$info->tool_id],
                ['testitem_id','=',$item->item_id],
                ['ishide','=',0],
            ])->value('jishu_yaoqiu');
            $key_condition = DB::table('a_condition_key')->select('id','key','unit')->where([
                ['std_id','=',$info->std_id],
                ['tool_id','=',$info->tool_id],
                ['item_id','=',$item->item_id]
            ])->orderBy('sort')->get();
            $std_content = '';
            foreach ($key_condition as $k => $v){
                $val_condition = DB::table('a_condition')->where('condition_key_id','=',$v->id)->whereIn('vol_class_id',[0,$info->vol_id,$info->class_id])->pluck('val')->toArray();
                $val_condition = implode($val_condition,' / ');
                $std_content .= "{$v->key}：$val_condition{$v->unit}；";
            }
            $std_content .=  "要求：$yaoqiu";
            $height = $this->toRowHeight($std_content,29,$initHeight);
            $worksheet->setCellValue('G'.$rowIndex,$std_content);
            /***************************检验标准  end******************************/
            /***************************原始记录  start******************************/
            $input_data = '';
            $key_data = DB::table('a_testdata_key as a')
                ->leftJoin('a_std_tool_item_rel as b','a.std_tool_item_rel_id','=','b.id')
                ->select('a.id','a.data_key','a.data_unit')->where([
                    ['b.std_id','=',$info->std_id],
                    ['b.tool_id','=',$info->tool_id],
                    ['b.testitem_id','=',$item->item_id]
                ])->orderBy('a.sort')->get();
            foreach ($key_data as $k => $v) {
                $input = DB::table('a_input_data')->where([
                    ['sample_info_id','=',$id],
                    ['testdata_key_id','=',$v->id]
                ])->first();
                if ($input) {
                    $input_data_v= !empty($input->val) ? $input->val : "　";
                    $input_data .= "{$v->data_key}：{$input_data_v}{$v->data_unit}；";
                }
            }
            $height = $this->toRowHeight($input_data,29,$height);
            $worksheet->setCellValue('J'.$rowIndex,$input_data);
            /***************************原始记录  end******************************/
            $worksheet->setCellValue('M'.$rowIndex,$item->res == 1 ? '合格' : ($item->res == 0 ? '不合格' : ''));
            $worksheet->setCellValue('O'.$rowIndex,$item->remark);
            $worksheet->getRowDimension($rowIndex)->setRowHeight($height);
            $rowIndex += 1;
        }

        $worksheet->setTitle($info->tool_name.substr($info->bar_code,6));
        // dd($worksheet->getPageSetup());
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                ],
            ],
        ];
        if ($storeFile) {
            $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
            $disk = Storage::disk('public');
            $disk->put($storeFile,''); //创建路径
            $writer->save($disk->path($storeFile));
            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet);
            return $disk->path($storeFile);
        }else{
            $file_name = $info->tool_name.$info->bar_code.'.xls';
            return self::saveDown($spreadsheet,$file_name);
        }
    }

    //计算excel文字行高
    private function toRowHeight($str,$lineChrCount=13,$oldHeith=25){
        $length = (strlen($str)+mb_strlen($str,"UTF8"))/2;  //计算字符串长度（中文算2个字符，英文数字算一个字符）
        $toHeight = ceil($length/$lineChrCount)*16;
        return $toHeight > $oldHeith ? $toHeight : $oldHeith;
    }

}
